from DataAccess.DBConnFactory import DBConnFactory
from Misc.Utils import *

from string import Template

def query_Mkt_Val(start_date, end_date, port_list=None):

	sql_tpl = Template('''select me.ref_date as ref_date,
						   me.portfolio_id as portfolio_id,
						   sum(me.amount * me.mkt_price * me.point_value / fx.price) as mkt_val
					  from mkt_expo me
					  left join portfolio_info pi on pi.portfolio_id = me.portfolio_id
					  left join comm_fx_latest fx on fx.ref_date = me.ref_date
												 and fx.dom_curncy = me.price_currency
												 and fx.for_curncy = pi.currency
					 where me.security_type <> 'CASH'
					   and me.security_type <> 'REPO'
					   and me.ref_date >= TO_DATE('${START}', 'yyyy-mm-dd')
					   and me.ref_date <= TO_DATE('${END}', 'yyyy-mm-dd')
					   and ${PORTF_CRITERIA}
					 group by me.ref_date, me.portfolio_id
					 order by me.portfolio_id, me.ref_date''')
	
	sql_text = sql_tpl.substitute(START=start_date.isoformat(),
								END=end_date.isoformat(),
								PORTF_CRITERIA=format_sql_criteria('me.portfolio_id', port_list))
	
	cursor = DBConnFactory().get_db_connection('PKEDB').cursor()							
	cursor.execute(sql_text)
	return cursor.fetchall()